{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reference article to convert data to hdf5\n",
    "# https://nbviewer.jupyter.org/github/vaexio/vaex-examples/blob/master/medium-airline-data-eda/airline-original-data-conversion.ipynb\n",
    "\n",
    "# Data source form https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data-page\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import vaex\n",
    "\n",
    "file_name = \"https://raw.githubusercontent.com/ironmussa/Optimus/master/examples/data/crime.csv\"\n",
    "col_name =\"INCIDENT_NUMBER\"\n",
    "df = vaex.from_csv(file_name, encoding=\"latin1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df[col_name].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df = vaex.from_csv(\"output/csv/yellow_tripdata_2009-01.csv\")\n",
    "df = vaex.from_csv(file_name, encoding=\"latin1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = vaex.open(file_export)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.43 s, sys: 23.9 ms, total: 1.45 s\n",
      "Wall time: 1.45 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "I162030584    13\n",
       "I152080623    11\n",
       "I172013170    10\n",
       "I182065208    10\n",
       "I172096394    10\n",
       "              ..\n",
       "I172059460     1\n",
       "I172059458     1\n",
       "I172059457     1\n",
       "I172059456     1\n",
       "142052550      1\n",
       "Length: 282517, dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "col_name =\"INCIDENT_NUMBER\"\n",
    "df[col_name].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pdf = pd.read_csv(file_name, encoding=\"latin1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 538 ms, sys: 3.87 ms, total: 542 ms\n",
      "Wall time: 422 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "I162030584    13\n",
       "I152080623    11\n",
       "I182065208    10\n",
       "I172013170    10\n",
       "I172096394    10\n",
       "              ..\n",
       "I162089808     1\n",
       "I162068201     1\n",
       "I152068233     1\n",
       "I162018863     1\n",
       "I182046935     1\n",
       "Name: INCIDENT_NUMBER, Length: 282517, dtype: int64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "pdf[col_name].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 196 ms, sys: 12.3 ms, total: 208 ms\n",
      "Wall time: 128 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                                 </th><th>name           </th><th>count  </th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i>     </td><td>Aachen         </td><td>1      </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i>     </td><td>Aarhus         </td><td>1      </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i>     </td><td>Abajo          </td><td>1      </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>3</i>     </td><td>Abar al' Uj 001</td><td>1      </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>4</i>     </td><td>Abbott         </td><td>1      </td></tr>\n",
       "<tr><td>...                               </td><td>...            </td><td>...    </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>45,711</i></td><td>Österplana 062 </td><td>1      </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>45,712</i></td><td>Österplana 063 </td><td>1      </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>45,713</i></td><td>Österplana 064 </td><td>1      </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>45,714</i></td><td>Łowicz         </td><td>1      </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>45,715</i></td><td>Święcany       </td><td>1      </td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "#       name             count\n",
       "0       Aachen           1\n",
       "1       Aarhus           1\n",
       "2       Abajo            1\n",
       "3       Abar al' Uj 001  1\n",
       "4       Abbott           1\n",
       "...     ...              ...\n",
       "45,711  Österplana 062   1\n",
       "45,712  Österplana 063   1\n",
       "45,713  Österplana 064   1\n",
       "45,714  Łowicz           1\n",
       "45,715  Święcany         1"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "df.groupby(\"name\").agg({'name':'count'}).sort(\"name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                            </th><th>vendor_name  </th><th>Trip_Pickup_DateTime  </th><th>Trip_Dropoff_DateTime  </th><th style=\"text-align: right;\">  Passenger_Count</th><th style=\"text-align: right;\">  Trip_Distance</th><th style=\"text-align: right;\">  Start_Lon</th><th style=\"text-align: right;\">  Start_Lat</th><th style=\"text-align: right;\">  Rate_Code</th><th style=\"text-align: right;\">  store_and_forward</th><th style=\"text-align: right;\">  End_Lon</th><th style=\"text-align: right;\">  End_Lat</th><th>Payment_Type  </th><th style=\"text-align: right;\">  Fare_Amt</th><th style=\"text-align: right;\">  surcharge</th><th style=\"text-align: right;\">  mta_tax</th><th style=\"text-align: right;\">  Tip_Amt</th><th style=\"text-align: right;\">  Tolls_Amt</th><th style=\"text-align: right;\">  Total_Amt</th><th style=\"text-align: right;\">  index</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i></td><td>VTS          </td><td>2009-01-04 02:52:00   </td><td>2009-01-04 03:02:00    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           2.63</td><td style=\"text-align: right;\">   -73.992 </td><td style=\"text-align: right;\">    40.7216</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9938</td><td style=\"text-align: right;\">  40.6959</td><td>CASH          </td><td style=\"text-align: right;\">       8.9</td><td style=\"text-align: right;\">        0.5</td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     0   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       9.4 </td><td style=\"text-align: right;\">      0</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i></td><td>VTS          </td><td>2009-01-04 03:31:00   </td><td>2009-01-04 03:38:00    </td><td style=\"text-align: right;\">                3</td><td style=\"text-align: right;\">           4.55</td><td style=\"text-align: right;\">   -73.9821</td><td style=\"text-align: right;\">    40.7363</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9558</td><td style=\"text-align: right;\">  40.768 </td><td>Credit        </td><td style=\"text-align: right;\">      12.1</td><td style=\"text-align: right;\">        0.5</td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     2   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">      14.6 </td><td style=\"text-align: right;\">      1</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i></td><td>VTS          </td><td>2009-01-03 15:43:00   </td><td>2009-01-03 15:57:00    </td><td style=\"text-align: right;\">                5</td><td style=\"text-align: right;\">          10.35</td><td style=\"text-align: right;\">   -74.0026</td><td style=\"text-align: right;\">    40.7397</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.87  </td><td style=\"text-align: right;\">  40.7702</td><td>Credit        </td><td style=\"text-align: right;\">      23.7</td><td style=\"text-align: right;\">        0  </td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     4.74</td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">      28.44</td><td style=\"text-align: right;\">      2</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>3</i></td><td>DDS          </td><td>2009-01-01 20:52:58   </td><td>2009-01-01 21:14:00    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           5   </td><td style=\"text-align: right;\">   -73.9743</td><td style=\"text-align: right;\">    40.791 </td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9966</td><td style=\"text-align: right;\">  40.7318</td><td>CREDIT        </td><td style=\"text-align: right;\">      14.9</td><td style=\"text-align: right;\">        0.5</td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     3.05</td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">      18.45</td><td style=\"text-align: right;\">      3</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>4</i></td><td>DDS          </td><td>2009-01-24 16:18:23   </td><td>2009-01-24 16:24:56    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           0.4 </td><td style=\"text-align: right;\">   -74.0016</td><td style=\"text-align: right;\">    40.7194</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -74.0084</td><td style=\"text-align: right;\">  40.7204</td><td>CASH          </td><td style=\"text-align: right;\">       3.7</td><td style=\"text-align: right;\">        0  </td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     0   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       3.7 </td><td style=\"text-align: right;\">      4</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>5</i></td><td>DDS          </td><td>2009-01-16 22:35:59   </td><td>2009-01-16 22:43:35    </td><td style=\"text-align: right;\">                2</td><td style=\"text-align: right;\">           1.2 </td><td style=\"text-align: right;\">   -73.9898</td><td style=\"text-align: right;\">    40.735 </td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.985 </td><td style=\"text-align: right;\">  40.7245</td><td>CASH          </td><td style=\"text-align: right;\">       6.1</td><td style=\"text-align: right;\">        0.5</td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     0   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       6.6 </td><td style=\"text-align: right;\">      5</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>6</i></td><td>DDS          </td><td>2009-01-21 08:55:57   </td><td>2009-01-21 09:05:42    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           0.4 </td><td style=\"text-align: right;\">   -73.984 </td><td style=\"text-align: right;\">    40.7435</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9803</td><td style=\"text-align: right;\">  40.7489</td><td>CREDIT        </td><td style=\"text-align: right;\">       5.7</td><td style=\"text-align: right;\">        0  </td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     1   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       6.7 </td><td style=\"text-align: right;\">      6</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>7</i></td><td>VTS          </td><td>2009-01-04 04:31:00   </td><td>2009-01-04 04:36:00    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           1.72</td><td style=\"text-align: right;\">   -73.9926</td><td style=\"text-align: right;\">    40.7484</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9956</td><td style=\"text-align: right;\">  40.7283</td><td>CASH          </td><td style=\"text-align: right;\">       6.1</td><td style=\"text-align: right;\">        0.5</td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     0   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       6.6 </td><td style=\"text-align: right;\">      7</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>8</i></td><td>CMT          </td><td>2009-01-05 16:29:02   </td><td>2009-01-05 16:40:21    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           1.6 </td><td style=\"text-align: right;\">   -73.9697</td><td style=\"text-align: right;\">    40.7492</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9904</td><td style=\"text-align: right;\">  40.7511</td><td>Credit        </td><td style=\"text-align: right;\">       8.7</td><td style=\"text-align: right;\">        0  </td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     1.3 </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">      10   </td><td style=\"text-align: right;\">      8</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>9</i></td><td>CMT          </td><td>2009-01-05 18:53:13   </td><td>2009-01-05 18:57:45    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           0.7 </td><td style=\"text-align: right;\">   -73.9552</td><td style=\"text-align: right;\">    40.783 </td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9586</td><td style=\"text-align: right;\">  40.7748</td><td>Cash          </td><td style=\"text-align: right;\">       5.9</td><td style=\"text-align: right;\">        0  </td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     0   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       5.9 </td><td style=\"text-align: right;\">      9</td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  #  vendor_name    Trip_Pickup_DateTime    Trip_Dropoff_DateTime      Passenger_Count    Trip_Distance    Start_Lon    Start_Lat    Rate_Code    store_and_forward    End_Lon    End_Lat  Payment_Type      Fare_Amt    surcharge    mta_tax    Tip_Amt    Tolls_Amt    Total_Amt    index\n",
       "  0  VTS            2009-01-04 02:52:00     2009-01-04 03:02:00                      1             2.63     -73.992       40.7216          nan                  nan   -73.9938    40.6959  CASH                   8.9          0.5        nan       0               0         9.4         0\n",
       "  1  VTS            2009-01-04 03:31:00     2009-01-04 03:38:00                      3             4.55     -73.9821      40.7363          nan                  nan   -73.9558    40.768   Credit                12.1          0.5        nan       2               0        14.6         1\n",
       "  2  VTS            2009-01-03 15:43:00     2009-01-03 15:57:00                      5            10.35     -74.0026      40.7397          nan                  nan   -73.87      40.7702  Credit                23.7          0          nan       4.74            0        28.44        2\n",
       "  3  DDS            2009-01-01 20:52:58     2009-01-01 21:14:00                      1             5        -73.9743      40.791           nan                  nan   -73.9966    40.7318  CREDIT                14.9          0.5        nan       3.05            0        18.45        3\n",
       "  4  DDS            2009-01-24 16:18:23     2009-01-24 16:24:56                      1             0.4      -74.0016      40.7194          nan                  nan   -74.0084    40.7204  CASH                   3.7          0          nan       0               0         3.7         4\n",
       "  5  DDS            2009-01-16 22:35:59     2009-01-16 22:43:35                      2             1.2      -73.9898      40.735           nan                  nan   -73.985     40.7245  CASH                   6.1          0.5        nan       0               0         6.6         5\n",
       "  6  DDS            2009-01-21 08:55:57     2009-01-21 09:05:42                      1             0.4      -73.984       40.7435          nan                  nan   -73.9803    40.7489  CREDIT                 5.7          0          nan       1               0         6.7         6\n",
       "  7  VTS            2009-01-04 04:31:00     2009-01-04 04:36:00                      1             1.72     -73.9926      40.7484          nan                  nan   -73.9956    40.7283  CASH                   6.1          0.5        nan       0               0         6.6         7\n",
       "  8  CMT            2009-01-05 16:29:02     2009-01-05 16:40:21                      1             1.6      -73.9697      40.7492          nan                  nan   -73.9904    40.7511  Credit                 8.7          0          nan       1.3             0        10           8\n",
       "  9  CMT            2009-01-05 18:53:13     2009-01-05 18:57:45                      1             0.7      -73.9552      40.783           nan                  nan   -73.9586    40.7748  Cash                   5.9          0          nan       0               0         5.9         9"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'DataFrameArrays' object has no attribute 'firstName'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-3-c38f7621988b>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfirstName\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlower\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/vaex/dataframe.py\u001b[0m in \u001b[0;36m__getattr__\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m    227\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__hidden__\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__get__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    228\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 229\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mobject\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__getattribute__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    230\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    231\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mproperty\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAttributeError\u001b[0m: 'DataFrameArrays' object has no attribute 'firstName'"
     ]
    }
   ],
   "source": [
    "df.firstName.str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import vaex\n",
    "df = vaex.open(\"output/hdf5/yellow_tripdata_2009-01.hdf5\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                            </th><th>vendor_name  </th><th>Trip_Pickup_DateTime  </th><th>Trip_Dropoff_DateTime  </th><th style=\"text-align: right;\">  Passenger_Count</th><th style=\"text-align: right;\">  Trip_Distance</th><th style=\"text-align: right;\">  Start_Lon</th><th style=\"text-align: right;\">  Start_Lat</th><th style=\"text-align: right;\">  Rate_Code</th><th style=\"text-align: right;\">  store_and_forward</th><th style=\"text-align: right;\">  End_Lon</th><th style=\"text-align: right;\">  End_Lat</th><th>Payment_Type  </th><th style=\"text-align: right;\">  Fare_Amt</th><th style=\"text-align: right;\">  surcharge</th><th style=\"text-align: right;\">  mta_tax</th><th style=\"text-align: right;\">  Tip_Amt</th><th style=\"text-align: right;\">  Tolls_Amt</th><th style=\"text-align: right;\">  Total_Amt</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i></td><td>VTS          </td><td>2009-01-04 02:52:00   </td><td>2009-01-04 03:02:00    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           2.63</td><td style=\"text-align: right;\">   -73.992 </td><td style=\"text-align: right;\">    40.7216</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9938</td><td style=\"text-align: right;\">  40.6959</td><td>CASH          </td><td style=\"text-align: right;\">       8.9</td><td style=\"text-align: right;\">        0.5</td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     0   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       9.4 </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i></td><td>VTS          </td><td>2009-01-04 03:31:00   </td><td>2009-01-04 03:38:00    </td><td style=\"text-align: right;\">                3</td><td style=\"text-align: right;\">           4.55</td><td style=\"text-align: right;\">   -73.9821</td><td style=\"text-align: right;\">    40.7363</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9558</td><td style=\"text-align: right;\">  40.768 </td><td>Credit        </td><td style=\"text-align: right;\">      12.1</td><td style=\"text-align: right;\">        0.5</td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     2   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">      14.6 </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i></td><td>VTS          </td><td>2009-01-03 15:43:00   </td><td>2009-01-03 15:57:00    </td><td style=\"text-align: right;\">                5</td><td style=\"text-align: right;\">          10.35</td><td style=\"text-align: right;\">   -74.0026</td><td style=\"text-align: right;\">    40.7397</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.87  </td><td style=\"text-align: right;\">  40.7702</td><td>Credit        </td><td style=\"text-align: right;\">      23.7</td><td style=\"text-align: right;\">        0  </td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     4.74</td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">      28.44</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>3</i></td><td>DDS          </td><td>2009-01-01 20:52:58   </td><td>2009-01-01 21:14:00    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           5   </td><td style=\"text-align: right;\">   -73.9743</td><td style=\"text-align: right;\">    40.791 </td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9966</td><td style=\"text-align: right;\">  40.7318</td><td>CREDIT        </td><td style=\"text-align: right;\">      14.9</td><td style=\"text-align: right;\">        0.5</td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     3.05</td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">      18.45</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>4</i></td><td>DDS          </td><td>2009-01-24 16:18:23   </td><td>2009-01-24 16:24:56    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           0.4 </td><td style=\"text-align: right;\">   -74.0016</td><td style=\"text-align: right;\">    40.7194</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -74.0084</td><td style=\"text-align: right;\">  40.7204</td><td>CASH          </td><td style=\"text-align: right;\">       3.7</td><td style=\"text-align: right;\">        0  </td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     0   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       3.7 </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>5</i></td><td>DDS          </td><td>2009-01-16 22:35:59   </td><td>2009-01-16 22:43:35    </td><td style=\"text-align: right;\">                2</td><td style=\"text-align: right;\">           1.2 </td><td style=\"text-align: right;\">   -73.9898</td><td style=\"text-align: right;\">    40.735 </td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.985 </td><td style=\"text-align: right;\">  40.7245</td><td>CASH          </td><td style=\"text-align: right;\">       6.1</td><td style=\"text-align: right;\">        0.5</td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     0   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       6.6 </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>6</i></td><td>DDS          </td><td>2009-01-21 08:55:57   </td><td>2009-01-21 09:05:42    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           0.4 </td><td style=\"text-align: right;\">   -73.984 </td><td style=\"text-align: right;\">    40.7435</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9803</td><td style=\"text-align: right;\">  40.7489</td><td>CREDIT        </td><td style=\"text-align: right;\">       5.7</td><td style=\"text-align: right;\">        0  </td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     1   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       6.7 </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>7</i></td><td>VTS          </td><td>2009-01-04 04:31:00   </td><td>2009-01-04 04:36:00    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           1.72</td><td style=\"text-align: right;\">   -73.9926</td><td style=\"text-align: right;\">    40.7484</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9956</td><td style=\"text-align: right;\">  40.7283</td><td>CASH          </td><td style=\"text-align: right;\">       6.1</td><td style=\"text-align: right;\">        0.5</td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     0   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       6.6 </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>8</i></td><td>CMT          </td><td>2009-01-05 16:29:02   </td><td>2009-01-05 16:40:21    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           1.6 </td><td style=\"text-align: right;\">   -73.9697</td><td style=\"text-align: right;\">    40.7492</td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9904</td><td style=\"text-align: right;\">  40.7511</td><td>Credit        </td><td style=\"text-align: right;\">       8.7</td><td style=\"text-align: right;\">        0  </td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     1.3 </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">      10   </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>9</i></td><td>CMT          </td><td>2009-01-05 18:53:13   </td><td>2009-01-05 18:57:45    </td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">           0.7 </td><td style=\"text-align: right;\">   -73.9552</td><td style=\"text-align: right;\">    40.783 </td><td style=\"text-align: right;\">        nan</td><td style=\"text-align: right;\">                nan</td><td style=\"text-align: right;\"> -73.9586</td><td style=\"text-align: right;\">  40.7748</td><td>Cash          </td><td style=\"text-align: right;\">       5.9</td><td style=\"text-align: right;\">        0  </td><td style=\"text-align: right;\">      nan</td><td style=\"text-align: right;\">     0   </td><td style=\"text-align: right;\">          0</td><td style=\"text-align: right;\">       5.9 </td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  #  vendor_name    Trip_Pickup_DateTime    Trip_Dropoff_DateTime      Passenger_Count    Trip_Distance    Start_Lon    Start_Lat    Rate_Code    store_and_forward    End_Lon    End_Lat  Payment_Type      Fare_Amt    surcharge    mta_tax    Tip_Amt    Tolls_Amt    Total_Amt\n",
       "  0  VTS            2009-01-04 02:52:00     2009-01-04 03:02:00                      1             2.63     -73.992       40.7216          nan                  nan   -73.9938    40.6959  CASH                   8.9          0.5        nan       0               0         9.4\n",
       "  1  VTS            2009-01-04 03:31:00     2009-01-04 03:38:00                      3             4.55     -73.9821      40.7363          nan                  nan   -73.9558    40.768   Credit                12.1          0.5        nan       2               0        14.6\n",
       "  2  VTS            2009-01-03 15:43:00     2009-01-03 15:57:00                      5            10.35     -74.0026      40.7397          nan                  nan   -73.87      40.7702  Credit                23.7          0          nan       4.74            0        28.44\n",
       "  3  DDS            2009-01-01 20:52:58     2009-01-01 21:14:00                      1             5        -73.9743      40.791           nan                  nan   -73.9966    40.7318  CREDIT                14.9          0.5        nan       3.05            0        18.45\n",
       "  4  DDS            2009-01-24 16:18:23     2009-01-24 16:24:56                      1             0.4      -74.0016      40.7194          nan                  nan   -74.0084    40.7204  CASH                   3.7          0          nan       0               0         3.7\n",
       "  5  DDS            2009-01-16 22:35:59     2009-01-16 22:43:35                      2             1.2      -73.9898      40.735           nan                  nan   -73.985     40.7245  CASH                   6.1          0.5        nan       0               0         6.6\n",
       "  6  DDS            2009-01-21 08:55:57     2009-01-21 09:05:42                      1             0.4      -73.984       40.7435          nan                  nan   -73.9803    40.7489  CREDIT                 5.7          0          nan       1               0         6.7\n",
       "  7  VTS            2009-01-04 04:31:00     2009-01-04 04:36:00                      1             1.72     -73.9926      40.7484          nan                  nan   -73.9956    40.7283  CASH                   6.1          0.5        nan       0               0         6.6\n",
       "  8  CMT            2009-01-05 16:29:02     2009-01-05 16:40:21                      1             1.6      -73.9697      40.7492          nan                  nan   -73.9904    40.7511  Credit                 8.7          0          nan       1.3             0        10\n",
       "  9  CMT            2009-01-05 18:53:13     2009-01-05 18:57:45                      1             0.7      -73.9552      40.783           nan                  nan   -73.9586    40.7748  Cash                   5.9          0          nan       0               0         5.9"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[  0 113]\n"
     ]
    }
   ],
   "source": [
    "column = \"Passenger_Count\"\n",
    "mn = df.minmax([column])[0]\n",
    "print(mn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[14092412        0        0        0        0        0        0        0\n",
      "        0        0]\n",
      "CPU times: user 190 ms, sys: 0 ns, total: 190 ms\n",
      "Wall time: 59.2 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "## Histogram\n",
    "print(df.count(df[column], binby = [df[column]], shape=10, limits=mn))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 521 ms, sys: 0 ns, total: 521 ms\n",
      "Wall time: 162 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>#                            </th><th style=\"text-align: right;\">  Passenger_Count</th><th style=\"text-align: right;\">           count</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td><i style='opacity: 0.6'>0</i></td><td style=\"text-align: right;\">                1</td><td style=\"text-align: right;\">     9.49342e+06</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>1</i></td><td style=\"text-align: right;\">                2</td><td style=\"text-align: right;\">     2.34716e+06</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>2</i></td><td style=\"text-align: right;\">                5</td><td style=\"text-align: right;\">     1.26336e+06</td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>3</i></td><td style=\"text-align: right;\">                3</td><td style=\"text-align: right;\">624652          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>4</i></td><td style=\"text-align: right;\">                4</td><td style=\"text-align: right;\">299288          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>5</i></td><td style=\"text-align: right;\">                6</td><td style=\"text-align: right;\"> 63817          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>6</i></td><td style=\"text-align: right;\">                0</td><td style=\"text-align: right;\">   718          </td></tr>\n",
       "<tr><td><i style='opacity: 0.6'>7</i></td><td style=\"text-align: right;\">              113</td><td style=\"text-align: right;\">     1          </td></tr>\n",
       "</tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  #    Passenger_Count             count\n",
       "  0                  1       9.49342e+06\n",
       "  1                  2       2.34716e+06\n",
       "  2                  5       1.26336e+06\n",
       "  3                  3  624652\n",
       "  4                  4  299288\n",
       "  5                  6   63817\n",
       "  6                  0     718\n",
       "  7                113       1"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "df.groupby(by=column).agg({column:\"count\"}).sort(by=\"count\",ascending= False)[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 7 µs, sys: 1 µs, total: 8 µs\n",
      "Wall time: 13.4 µs\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array(1.70010963)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time\n",
    "df.mean(column)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "10.0",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_slice_bound\u001b[0;34m(self, label, side, kind)\u001b[0m\n\u001b[1;32m   5166\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5167\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_searchsorted_monotonic\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlabel\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mside\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5168\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36m_searchsorted_monotonic\u001b[0;34m(self, label, side)\u001b[0m\n\u001b[1;32m   5127\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5128\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"index must be monotonic increasing or decreasing\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5129\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mValueError\u001b[0m: index must be monotonic increasing or decreasing",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<timed eval>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/series.py\u001b[0m in \u001b[0;36m__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   1108\u001b[0m             \u001b[0mkey\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcheck_bool_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1109\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1110\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_with\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1111\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1112\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_get_with\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/series.py\u001b[0m in \u001b[0;36m_get_with\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   1113\u001b[0m         \u001b[0;31m# other: fancy integer or otherwise\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1114\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mslice\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1115\u001b[0;31m             \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_convert_slice_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkind\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"getitem\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1116\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_values\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1117\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mABCDataFrame\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/numeric.py\u001b[0m in \u001b[0;36m_convert_slice_indexer\u001b[0;34m(self, key, kind)\u001b[0m\n\u001b[1;32m    395\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    396\u001b[0m         \u001b[0;31m# translate to locations\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 397\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mslice_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstart\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstop\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstep\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkind\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mkind\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    398\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    399\u001b[0m     def _format_native_types(\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mslice_indexer\u001b[0;34m(self, start, end, step, kind)\u001b[0m\n\u001b[1;32m   5032\u001b[0m         \u001b[0mslice\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m3\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5033\u001b[0m         \"\"\"\n\u001b[0;32m-> 5034\u001b[0;31m         \u001b[0mstart_slice\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mend_slice\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mslice_locs\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstart\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mend\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstep\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mstep\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkind\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mkind\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5035\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5036\u001b[0m         \u001b[0;31m# return a slice\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mslice_locs\u001b[0;34m(self, start, end, step, kind)\u001b[0m\n\u001b[1;32m   5252\u001b[0m         \u001b[0mend_slice\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5253\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mend\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5254\u001b[0;31m             \u001b[0mend_slice\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_slice_bound\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mend\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"right\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkind\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5255\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mend_slice\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5256\u001b[0m             \u001b[0mend_slice\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_slice_bound\u001b[0;34m(self, label, side, kind)\u001b[0m\n\u001b[1;32m   5168\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5169\u001b[0m                 \u001b[0;31m# raise the original KeyError\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5170\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5171\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5172\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mslc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mndarray\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_slice_bound\u001b[0;34m(self, label, side, kind)\u001b[0m\n\u001b[1;32m   5162\u001b[0m         \u001b[0;31m# we need to look up the label\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5163\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5164\u001b[0;31m             \u001b[0mslc\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlabel\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5165\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5166\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/numeric.py\u001b[0m in \u001b[0;36mget_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m    477\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mTypeError\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mNotImplementedError\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    478\u001b[0m             \u001b[0;32mpass\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 479\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0msuper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtolerance\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtolerance\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    480\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    481\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mcache_readonly\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   2897\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2898\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2899\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_maybe_cast_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2900\u001b[0m         \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtolerance\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtolerance\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2901\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mindexer\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mndim\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m1\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mindexer\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msize\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.Float64HashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.Float64HashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 10.0"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df[\"Start_Lon\"].value_counts()[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 10.7 ms, sys: 1.04 ms, total: 11.7 ms\n",
      "Wall time: 48.9 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'vendor_name': array(['VTS', 'DDS', 'VTS', 'VTS', 'DDS'], dtype=object),\n",
       " 'Trip_Pickup_DateTime': array(['2009-01-21 14:48:00', '2009-01-05 12:26:42',\n",
       "        '2009-01-21 14:53:00', '2009-01-21 07:22:00',\n",
       "        '2009-01-06 22:04:38'], dtype=object),\n",
       " 'Trip_Dropoff_DateTime': array(['2009-01-21 14:57:00', '2009-01-05 12:28:45',\n",
       "        '2009-01-21 15:01:00', '2009-01-21 07:27:00',\n",
       "        '2009-01-06 22:15:48'], dtype=object),\n",
       " 'Passenger_Count': array([5, 1, 1, 5, 1]),\n",
       " 'Trip_Distance': array([1.97, 0.  , 1.61, 0.62, 3.  ]),\n",
       " 'Start_Lon': array([-74.00372 , -73.950389, -73.961167, -73.977122, -73.96716 ]),\n",
       " 'Start_Lat': array([40.747423, 40.752603, 40.78045 , 40.755853, 40.756639]),\n",
       " 'Rate_Code': array([nan, nan, nan, nan, nan]),\n",
       " 'store_and_forward': array([nan, nan, nan, nan, nan]),\n",
       " 'End_Lon': array([-74.000782, -73.950389, -73.978517, -73.983008, -73.984548]),\n",
       " 'End_Lat': array([40.723958, 40.752603, 40.77272 , 40.760228, 40.720279]),\n",
       " 'Payment_Type': array(['CASH', 'CASH', 'CASH', 'CASH', 'CREDIT'], dtype=object),\n",
       " 'Fare_Amt': array([7.3, 2.5, 6.9, 4.5, 8.9]),\n",
       " 'surcharge': array([0. , 0. , 0. , 0. , 0.5]),\n",
       " 'mta_tax': array([nan, nan, nan, nan, nan]),\n",
       " 'Tip_Amt': array([0., 0., 0., 0., 3.]),\n",
       " 'Tolls_Amt': array([0., 0., 0., 0., 0.]),\n",
       " 'Total_Amt': array([ 7.3,  2.5,  6.9,  4.5, 12.4])}"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "df[20000:20005].to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4.2 ms, sys: 30 µs, total: 4.23 ms\n",
      "Wall time: 3.95 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'vendor_name': array(['CMT', 'CMT', 'DDS', 'CMT', 'CMT', 'CMT', 'CMT', 'CMT', 'DDS',\n",
       "        'CMT'], dtype=object),\n",
       " 'Trip_Pickup_DateTime': array(['2009-01-05 08:15:38', '2009-01-05 06:21:43',\n",
       "        '2009-01-20 13:44:02', '2009-01-05 16:19:53',\n",
       "        '2009-01-05 17:22:16', '2009-01-05 16:02:52',\n",
       "        '2009-01-05 12:15:06', '2009-01-05 07:49:57',\n",
       "        '2009-01-23 23:57:34', '2009-01-05 10:23:13'], dtype=object)}"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "df[\"vendor_name\",\"Trip_Pickup_DateTime\"][10:20].to_dict()\n",
    "# dir(df[\"vendor_name\",\"Trip_Pickup_DateTime\"][10:20])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import vaex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "# define the name of the directory to be created\n",
    "output_dir = \"./output/csv/\"\n",
    "output_dir_hdf5 = \"./output/hdf5/\"\n",
    "\n",
    "months = list(range(1,13))\n",
    "years = list(range(2009, 2020))\n",
    "\n",
    "file_names = []\n",
    "for year in years:\n",
    "    for month in months:\n",
    "        month = \"{0:0=2d}\".format(month)\n",
    "        file_names.append(f\"https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_{year}-{month}.csv\")        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Creation of the directory ./output/csv/ failed\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    os.mkdir(output_dir)\n",
    "except OSError:\n",
    "    print (\"Creation of the directory %s failed\" % output_dir)\n",
    "else:\n",
    "    print (\"Successfully created the directory %s \" % output_dir)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
